<?php
//by carboleda 20130927
class historicoabonosxmesModelo extends Modelo
{

	public function __construc(){
		parent::__construc();
	}

	public function cargarlista($lista, $filtro, $esMultiple) {
	    try {
	        $SQLSelectLista = "";
	        $sbReturn = "<option value='-1'>Seleccionar Valor</option>";

	        /*switch ($lista) {
	            case "almacen":
	                $SQLSelectLista = "select nosucursal as valor, nombre as etiqueta from sucursal ";
	                break;
                case "linea":
	                $SQLSelectLista = "select nolinea as valor, nombre as etiqueta from linea ";
	                break;
	            case "cliente":
	                $SQLSelectLista = "select nocliente as valor, cedula||' - '||nombre as etiqueta from cliente order by cedula";
	                break;
                case "departamento":
	                $SQLSelectLista = "select nodepartamento as valor, nombre as etiqueta from departamento ";
	                break;
                case "producto":
	                $SQLSelectLista = "select noproducto as valor, referencia||' - '||nombre as etiqueta from producto order by referencia";
	                break;
	            case "rutas":
	                $sbReturn = "";
	                $SQLSelectLista = " ";
	                break;            
	        }*/
	        $SQLSelectLista = "select valor, etiqueta from listas where lista = '".$lista."' ";

	        $bd_rc = $this->ejecutar( $SQLSelectLista );
			$no_rc = $this->contar( $bd_rc );

	        if ($no_rc > 0) {
	        	for( $i = 0; $i < $no_rc; $i++){
					$rg_rc = $this->buscaruno( $bd_rc, $i );

	                if($esMultiple === true) {
	                    $sbReturn.="<input type='checkbox' class='check".$lista."' value='" . trim($rg_rc['valor']) . "'>" . trim($rg_rc['valor']) . "</input>";
	                } else {
	                    $sbReturn.="<option value='" . trim($rg_rc['valor']) . "'>" . trim($rg_rc['etiqueta']) . "</option>";
	                }
	            }            
	            
	            if($esMultiple === true) {  
	                $sbReturn.="<input type='checkbox' value='todas' class='checkTodas'><strong>Todas</strong></input>";
	            }
	        }

	        return $sbReturn;
	    } catch (Exception $e) {
	        return null;
	    }
	}

	public function consultar_abonosxmes($fecha_inicial, $fecha_final, $almacen, $linea, $cliente, $departamento, $producto){
		$lstResultado = array();
		$where = "";
		
		if(isset($fecha_inicial) && isset($fecha_final)) {
			$where .= " AND fecha_abono between '".$fecha_inicial."' AND '".$fecha_final."' ";
		}
		if(isset($almacen) && $almacen !== "-1") {
			$where .= " AND suc_nosucursal = ".$almacen." ";
		}
		if(isset($linea) && $linea !== "-1") {
			$where .= " AND pro_nolinea = ".$linea." ";
		}
		if(isset($cliente) && $cliente !== "-1") {
			$where .= " AND nocliente = ".$cliente." ";
		}
		if(isset($departamento) && $departamento !== "-1") {
			$where .= " AND nodepartamento = ".$departamento." ";
		}
		if(isset($producto) && $producto !== "-1") {
			$where .= " AND det_fac_noproducto = ".$producto." ";
		}

		$sql = "SELECT ID, MAX(CANTIDAD) AS cant, MAX(MES) AS mes
				FROM (
					SELECT fecha_abono AS ID, COUNT(fecha_abono) AS CANTIDAD, NULL AS MES
					FROM (
						SELECT punto_pago, nofactura, nocliente, cli_cedula, cli_nombre, suc_nosucursal, suc_nombre, 
						cli_cupocredito, det_fac_noproducto, pro_nolinea, nodepartamento, dpto_nombre,
						vendedor, noforma_pago, fecha, DATE_PART('month', fecha_abono) AS fecha_abono
						FROM historico_abonos_x_mes
						WHERE 1=1 " . $where . "
						GROUP BY punto_pago, nofactura, nocliente, cli_cedula, cli_nombre, suc_nosucursal, suc_nombre, 
						cli_cupocredito, det_fac_noproducto, pro_nolinea, nodepartamento, dpto_nombre,
						vendedor, noforma_pago, fecha, DATE_PART('month', fecha_abono)
					) AS TB1					
					GROUP BY id, fecha_abono

					UNION ALL

					SELECT ID, CANTIDAD, MES
					FROM (
						SELECT 1 AS ID, 0 AS CANTIDAD, 'Enero' AS MES
						UNION
						SELECT 2 AS ID, 0 AS CANTIDAD, 'Febrero' AS MES
						UNION
						SELECT 3 AS ID, 0 AS CANTIDAD, 'Marzo' AS MES
						UNION
						SELECT 4 AS ID, 0 AS CANTIDAD, 'Abril' AS MES
						UNION
						SELECT 5 AS ID, 0 AS CANTIDAD, 'Mayo' AS MES
						UNION
						SELECT 6 AS ID, 0 AS CANTIDAD, 'Junio' AS MES
						UNION
						SELECT 7 AS ID, 0 AS CANTIDAD, 'Julio' AS MES
						UNION
						SELECT 8 AS ID, 0 AS CANTIDAD, 'Agosto' AS MES
						UNION
						SELECT 9 AS ID, 0 AS CANTIDAD, 'Septiembre' AS MES
						UNION
						SELECT 10 AS ID, 0 AS CANTIDAD, 'Octubre' AS MES
						UNION
						SELECT 11 AS ID, 0 AS CANTIDAD, 'Noviembre' AS MES
						UNION
						SELECT 12 AS ID, 0 AS CANTIDAD, 'Diciembre' AS MES
					) MESES
				) AS TB
				GROUP BY ID
				ORDER BY ID ";

		//echo $sql;

		$bd_rc = $this->ejecutar( $sql );
		$no_rc = $this->contar( $bd_rc );

		if ($no_rc > 0) {			
        	for( $i = 0; $i < $no_rc; $i++){
				$rg_rc = $this->buscaruno( $bd_rc, $i );
				$lstResultado[] = $rg_rc;
            }
        }

		return $lstResultado;
	}
}

?>